package com.java.dao.imp;

import com.java.bean.Courier;
import com.java.bean.Express;
import com.java.bean.User;
import com.java.dao.BaseUserDao;
import com.java.util.DruidUtil;

import java.sql.*;
import java.util.*;
import java.util.Date;

public class BaseUserDaoMysql implements BaseUserDao {

    Date date = new Date();

    //用于查询数据库中的全部用户（总数+新增）
    public static final String SQL_CONSOLE = "SELECT COUNT(ID) user_size,COUNT(TO_DAYS(CREATETIME)=TO_DAYS(NOW()) OR NULL) user_day FROM USER";

    //用于查询数据库中的所有用户信息
    public static final String SQL_FIND_ALL = "SELECT * FROM USER";
    //用于查询数据库中的单个用户信息
    public static final String SQL_FIND_BY_PHONE = "SELECT * FROM USER WHERE PHONE=?";
    //用于分页查询数据库中的用户信息
    public static final String SQL_FIND_LIMIT = "SELECT * FROM USER LIMIT ?,?";
    //录入用户
    public static final String SQL_INSERT = "INSERT INTO USER (NAME,PHONE,PASSWORD,CREATETIME,LOGINTIME) VALUES(?,?,?,?,?)";
    //用户修改
    public static final String SQL_UPDATE_BY_PHONE = "UPDATE USER SET NAME=?,PHONE=?,password=? WHERE phone=?";
    //用户的删除
    public static final String SQL_DELETE = "DELETE FROM USER WHERE PHONE=?";
    //根据手机号注册用户
    public static final String SQL_INSERT_BY_PHONE = "INSERT INTO USER(PHONE, CREATETIME, LOGINTIME) VALUES(?,?,?)";

    /**
     * 用于查询数据库中的全部用户（总数+新增）
     *
     * @return [{size:总数,day:新增},{size:总数,day:新增}]
     */
    @Override
    public List<Map<String, Integer>> console() {
        ArrayList<Map<String,Integer>> data = new ArrayList<>();
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.    预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_CONSOLE);
            //3.    填充参数(可选)
            //4.    执行SQL语句
            result = state.executeQuery();
            //5.    获取执行的结果
            if(result.next()){
                int user_size = result.getInt("user_size");
                int user_day = result.getInt("user_day");

                Map data1 = new HashMap();
                data1.put("user_size",user_size);
                data1.put("user_day",user_day);
                data.add(data1);
            }
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }

        return data;
    }

    /**
     * 用于查询所有用户
     *
     * @param limit      是否分页的标记，true表示分页。false表示查询所有用户
     * @param offset     SQL语句的起始索引
     * @param pageNumber 页查询的数量
     * @return 用户的集合
     */
    @Override
    public List<User> findAll(boolean limit, int offset, int pageNumber) {
        ArrayList<User> data = new ArrayList<>();
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.    预编译SQL语句
        try {
            if(limit) {
                state = conn.prepareStatement(SQL_FIND_LIMIT);
                //3.    填充参数(可选)
                state.setInt(1,offset);
                state.setInt(2,pageNumber);
            }else {
                state = conn.prepareStatement(SQL_FIND_ALL);
            }

            //4.    执行SQL语句
            result = state.executeQuery();
            //5.    获取执行的结果
            while(result.next()){
                int id = result.getInt("id");
                String name = result.getString("name");
                String phone = result.getString("phone");
                String password = result.getString("password");
                Timestamp createTime = result.getTimestamp("createTime");
                Timestamp loginTime = result.getTimestamp("loginTime");
                User e = new User(id,name,phone,password,createTime,loginTime);
                data.add(e);
            }
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }
        return data;
    }

    /**
     * 根据用户手机号码，查询他的信息
     *
     * @param phone 手机号码
     * @return 查询的用户信息列表
     */
    @Override
    public User findByPhone(String phone) {
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.    预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_FIND_BY_PHONE);
            //3.    填充参数(可选)
            state.setString(1,phone);
            //4.    执行SQL语句
            result = state.executeQuery();
            //5.    获取执行的结果
            if(result.next()){
                int id = result.getInt("id");
                String name = result.getString("name");
                String password = result.getString("password");
                Timestamp createTime = result.getTimestamp("createTime");
                Timestamp loginTime = result.getTimestamp("loginTime");
                User c = new User(id,name,phone,password,createTime,loginTime);
                return c;
            }
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }
        return null;
    }

    /**
     * 用户的修改
     *
     * @param phone   要修改的用户的手机号
     * @param newUser 新的用户对象（name，phone,password）
     * @return 修改的结果，true表示成功，false表示失败
     */
    @Override
    public boolean update(String phone, User newUser) {
        //1.    连接的获取
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.    预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_UPDATE_BY_PHONE);
            state.setString(1,newUser.getName());
            state.setString(2,newUser.getPhone());
            state.setString(3,newUser.getPassword());
            state.setString(4,phone);
            return state.executeUpdate()>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 用户的录入
     *
     * @param @return 录入的结果，true表示成功，false表示失败
     */
    @Override
    public boolean insert(User c) {
        //1.    连接的获取
        Connection conn = DruidUtil.getConnection();
        //2.    预编译SQL语句
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_INSERT);
            //3.    填充参数
            state.setString(1,c.getName());
            state.setString(2,c.getPhone());
            state.setString(3,c.getPassword());
            state.setDate(4, new java.sql.Date(date.getTime()));
            state.setDate(5, new java.sql.Date(date.getTime()));
            //4.    执行SQL语句,并获取执行结果
            return state.executeUpdate()>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            //5.    释放资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 根据手机号注册用户
     *
     * @param @return 录入的结果，true表示成功，false表示失败
     */
    @Override
    public boolean insertByPhone(String phone) {
        //1.    连接的获取
        Connection conn = DruidUtil.getConnection();
        //2.    预编译SQL语句
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_INSERT_BY_PHONE);
            //3.    填充参数
            state.setString(1, phone);
            state.setDate(2, new java.sql.Date(date.getTime()));
            state.setDate(3, new java.sql.Date(date.getTime()));
            //4.    执行SQL语句,并获取执行结果
            return state.executeUpdate()>0 ? true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            //5.    释放资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 根据phone，删除单个用户信息
     *
     * @param phone 要删除的用户手机号
     * @return 删除的结果，true表示成功，false表示失败
     */
    @Override
    public boolean delete(String phone) {
        //1.    连接的获取
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.    预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_DELETE);
            state.setString(1,phone);
            return state.executeUpdate()>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }
}
